import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import matplotlib.patches as patches
import os
from matplotlib.colors import LinearSegmentedColormap
from datetime import datetime
import matplotlib.patheffects as path_effects

# 设置中文字体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 定义常量 - 保存结果的目录
RESULTS_DIR = r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\results"


def load_erp_data():
    """加载ERP订单数据"""
    # 定义可能的文件路径
    possible_paths = [
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\data\erp_order_data.xlsx",
        r"D:\大三上\大数据分析及数据可视化\《Excel数据可视化 - 从图表到数据大屏》-清华-郭宏远\实验\erp_order_data.xlsx"
    ]

    for path in possible_paths:
        if os.path.exists(path):
            try:
                df = pd.read_excel(path)
                # 确保日期格式正确
                if 'order_time' in df.columns:
                    df['order_time'] = pd.to_datetime(df['order_time'])
                print(f"✓ 数据加载成功: {path}")
                return df
            except Exception as e:
                print(f"读取 {path} 失败: {e}")
                continue

    raise FileNotFoundError("未找到erp_order_data.xlsx文件，请确认文件是否存在或路径是否正确")


def process_product_data(df):
    """处理商品数据，对比两个年份销量"""
    # 确保order_time是datetime类型
    if 'order_time' not in df.columns:
        raise ValueError("数据中缺少'order_time'列，无法进行时间分析")

    # 确保product_name列存在
    if 'product_name' not in df.columns:
        raise ValueError("数据中缺少'product_name'列，无法进行商品分析")

    # 确保product_amount列存在
    if 'product_amount' not in df.columns:
        raise ValueError("数据中缺少'product_amount'列，无法计算销售金额")

    # 创建副本防止修改原始数据
    df = df.copy()

    # 提取年份
    df['year'] = df['order_time'].dt.year

    # 获取数据中的年份
    years = df['year'].unique()
    years = sorted(years)
    print(f"数据中包含的年份: {years}")

    if len(years) < 2:
        raise ValueError("数据中年份不足，需要至少两年的数据进行对比")

    # 选择最近的两个完整年份
    year1 = years[-2]  # 倒数第二年
    year2 = years[-1]  # 最新年份

    # 获取数据中的商品列表
    products = df['product_name'].unique()
    print(f"数据中包含的商品种类: {len(products)}种")

    # 按商品和年份分组计算销量
    product_sales = df.groupby(['product_name', 'year'])['product_amount'].sum().reset_index()

    # 重新组织数据，使每年的数据成为一列
    # 使用pivot_table确保正确的格式
    pivoted = product_sales.pivot_table(
        index='product_name',
        columns='year',
        values='product_amount',
        fill_value=0
    ).reset_index()

    # 重命名列
    pivoted.columns.name = None
    pivoted = pivoted.rename(columns={
        'product_name': 'product',
        year1: f'sales_{year1}',
        year2: f'sales_{year2}'
    })

    # 计算同比增长率
    pivoted['growth_rate'] = ((pivoted[f'sales_{year2}'] - pivoted[f'sales_{year1}']) / pivoted[f'sales_{year1}']) * 100

    # 只保留销量前5的商品
    pivoted = pivoted.nlargest(5, f'sales_{year1}')

    return pivoted, year1, year2


def create_comparison_chart():
    """创建商品销量对比柱形图"""
    # 加载数据
    df = load_erp_data()

    # 处理商品数据
    product_data, year1, year2 = process_product_data(df)

    # 提取数据
    products = product_data['product'].tolist()
    sales_prev = product_data[f'sales_{year1}'].tolist()
    sales_current = product_data[f'sales_{year2}'].tolist()
    growth_rates = product_data['growth_rate'].tolist()

    # 检查数据有效性
    if not products:
        print("错误：没有找到有效的商品数据。")
        return None, None

    # 创建渐变颜色映射
    blue_colors = ['#16213E', '#0F3460', '#1A5F7A', '#2C8C99', '#4BB5C2', '#7FD6E3']
    red_colors = ['#5C1E2B', '#8A2D3D', '#B73E4F', '#D54D5F', '#E56A72', '#F08B8F']

    # 创建颜色映射
    blue_cmap = LinearSegmentedColormap.from_list('blue', blue_colors, N=100)
    red_cmap = LinearSegmentedColormap.from_list('red', red_colors, N=100)

    # 创建图形 - 靛蓝色背景
    fig = plt.figure(figsize=(14, 9), facecolor='#1A1A2E')
    ax = fig.add_subplot(111, facecolor='#1A1A2E')

    # 设置x轴位置
    x_pos = np.arange(len(products))
    bar_width = 0.35

    # 绘制上一年销量（深蓝色）
    for i, (x, sale) in enumerate(zip(x_pos, sales_prev)):
        if sale <= 0:
            continue

        gradient_height = 80
        for j in range(gradient_height):
            height_segment = sale / gradient_height
            y_bottom = j * height_segment
            color = blue_cmap(1 - (j / gradient_height) * 0.7)
            rect = patches.Rectangle(
                (x - bar_width / 2, y_bottom), bar_width, height_segment,
                linewidth=0, facecolor=color, alpha=0.9
            )
            ax.add_patch(rect)

    # 绘制本年销量（红色）
    for i, (x, sale) in enumerate(zip(x_pos, sales_current)):
        if sale <= 0:
            continue

        gradient_height = 80
        for j in range(gradient_height):
            height_segment = sale / gradient_height
            y_bottom = j * height_segment
            color = red_cmap(1 - (j / gradient_height) * 0.7)
            rect = patches.Rectangle(
                (x + bar_width / 2, y_bottom), bar_width, height_segment,
                linewidth=0, facecolor=color, alpha=0.9
            )
            ax.add_patch(rect)

    # 添加白色边框
    for i in x_pos:
        # 上一年边框
        if sales_prev[i] > 0:
            ax.bar([i - bar_width / 2], [sales_prev[i]], width=bar_width, color='none', edgecolor='#E0E0E0',
                   linewidth=1.5)

        # 本年边框
        if sales_current[i] > 0:
            ax.bar([i + bar_width / 2], [sales_current[i]], width=bar_width, color='none', edgecolor='#E0E0E0',
                   linewidth=1.5)

    # 添加数据标签
    for i, (sale_prev, sale_current) in enumerate(zip(sales_prev, sales_current)):
        # 上一年标签
        if sale_prev > 0:
            ax.text(i - bar_width / 2, sale_prev * 0.95, f'{int(sale_prev)}',
                    ha='center', va='center', fontsize=12, fontweight='bold', color='#FFFFFF',
                    bbox=dict(boxstyle='round,pad=0.2', facecolor='#2C3E50', edgecolor='none', alpha=0.7))

        # 本年标签
        if sale_current > 0:
            ax.text(i + bar_width / 2, sale_current * 0.95, f'{int(sale_current)}',
                    ha='center', va='center', fontsize=12, fontweight='bold', color='#FFFFFF',
                    bbox=dict(boxstyle='round,pad=0.2', facecolor='#5C1E2B', edgecolor='none', alpha=0.7))

    # 添加箭头和差值
    for i, (sale_prev, sale_current) in enumerate(zip(sales_prev, sales_current)):
        # 计算差值
        diff = sale_current - sale_prev
        if abs(diff) < 1:  # 忽略小的差异
            continue

        # 计算箭头位置
        x_pos_arrow = i
        y_start = min(sale_prev, sale_current)
        y_end = max(sale_prev, sale_current)

        # 确定箭头方向
        if diff > 0:
            arrow_text = f'+{int(diff)}'
            arrow_color = '#E56A72'  # 红色，表示增长
        else:
            arrow_text = f'{int(diff)}'
            arrow_color = '#2C3E50'  # 蓝色，表示下降

        # 添加箭头
        ax.annotate('',
                    xy=(x_pos_arrow, y_end),
                    xytext=(x_pos_arrow, y_start),
                    arrowprops=dict(arrowstyle='->', color=arrow_color, linewidth=2, mutation_scale=20))

        # 添加差值文本
        ax.text(x_pos_arrow, (y_start + y_end) / 2, arrow_text,
                ha='center', va='center', fontsize=10, fontweight='bold', color='#FFFFFF',
                bbox=dict(boxstyle='round,pad=0.2', facecolor=arrow_color, edgecolor='none', alpha=0.7))

    # 设置坐标轴
    ax.set_ylabel('销售金额 (元)', fontsize=18, fontweight='bold', color='#E0E0E0')
    ax.set_xlabel('商品', fontsize=16, fontweight='bold', color='#E0E0E0')
    ax.set_xticks(x_pos)
    ax.set_xticklabels(products, fontsize=14, fontweight='bold', color='#E0E0E0')
    ax.tick_params(axis='y', labelsize=14, colors='#E0E0E0')

    # 美化坐标轴
    for spine in ax.spines.values():
        spine.set_color('#4A4A6A')
        spine.set_linewidth(2)

    # 设置网格线
    ax.grid(axis='y', alpha=0.3, linestyle='--', color='#4A4A6A')
    ax.set_axisbelow(True)

    # 设置y轴范围
    max_sales = max(max(sales_prev), max(sales_current)) * 1.2
    ax.set_ylim(0, max_sales)

    # 设置标题
    title_main = f'{year2}年商品对比{year1}年销售情况'

    # 分析最近的趋势
    total_prev = sum(sales_prev)
    total_current = sum(sales_current)
    growth_rate = ((total_current - total_prev) / total_prev * 100) if total_prev > 0 else 0

    # 找出下降最多的商品
    declining_rates = []
    for i, rate in enumerate(growth_rates):
        if rate < 0:  # 只考虑下降的商品
            declining_rates.append((products[i], rate))

    if declining_rates:
        most_declining_product, most_declining_rate = max(declining_rates, key=lambda x: abs(x[1]))
        subtitle = f'商品整体比{year1}年销量{"增长" if growth_rate > 0 else "下降"}{abs(growth_rate):.1f}%，其中{most_declining_product}下降最多，下降{abs(most_declining_rate):.1f}%'
    else:
        subtitle = f'商品整体比{year1}年销量{"增长" if growth_rate > 0 else "下降"}{abs(growth_rate):.1f}%，所有商品销量均{"增长" if growth_rate > 0 else "下降"}'

    ax.set_title(f'{title_main}\n{subtitle}',
                 fontsize=20, fontweight='bold', pad=25, color='#FFFFFF',
                 y=1.08)  # 调整标题位置防止重叠

    # 添加图例
    blue_patch = patches.Rectangle((0, 0), 1, 1, facecolor='#4BB5C2', edgecolor='#E0E0E0')
    red_patch = patches.Rectangle((0, 0), 1, 1, facecolor='#E56A72', edgecolor='#E0E0E0')
    ax.legend([blue_patch, red_patch], [f'{year1}年销量', f'{year2}年销量'],
              fontsize=14, frameon=False, loc='upper right',
              labelcolor='#E0E0E0', bbox_to_anchor=(0.98, 0.95))

    # 添加数据来源
    latest_date = df['order_time'].max()
    latest_date_str = latest_date.strftime('%Y.%m.%d')
    source_text = f'*注：数据来源于公司销售系统，统计日期截至{latest_date_str}'
    ax.text(0.02, 0.02, source_text, transform=ax.transAxes,
            fontsize=10, color='#B0B0B0', alpha=0.7, va='bottom')

    # 调整布局
    plt.tight_layout()

    # 确保结果目录存在
    os.makedirs(RESULTS_DIR, exist_ok=True)
    # 保存图片
    output_path = os.path.join(RESULTS_DIR, '09_对比柱形图.png')
    plt.savefig(output_path, dpi=300, bbox_inches='tight',
                facecolor='#1A1A2E', edgecolor='none')

    plt.show()

    # 数据分析
    print("对比柱形图数据分析：")
    print(f"- 数据覆盖商品：{len(products)}个")
    print(f"- {year2}年总销量：{total_current:.2f}元")
    print(f"- {year1}年总销量：{total_prev:.2f}元")
    print(f"- 整体同比增长率：{growth_rate:.1f}%")

    # 找出变化最大的商品
    if growth_rates:
        max_change_idx = np.argmax(np.abs(growth_rates))
        max_change_product = products[max_change_idx]
        max_change_rate = growth_rates[max_change_idx]
        print(f"- 变化最大的商品：{max_change_product}，变化率{max_change_rate:.1f}%")

    return fig, ax


# 执行代码
if __name__ == "__main__":
    try:
        fig, ax = create_comparison_chart()
    except Exception as e:
        print(f"图表生成失败: {e}")
        raise